#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2022/4/17 23:19
# @Author  : Grayson Liu
# @Email   : graysonliu@foxmail.com
# @File    : app.py
import json
import sqlite3

from flask import Flask, render_template, redirect, url_for, request, flash, jsonify, session, g
from werkzeug.security import check_password_hash, generate_password_hash

from config import Config

from jinja2 import Markup, Environment, FileSystemLoader
from pyecharts.globals import CurrentConfig, ThemeType, SymbolType

# 初始化pyecharts
from database import database_info, query_data, query_processor
from numpy import float64
import numpy as np

CurrentConfig.GLOBAL_ENV = Environment(loader=FileSystemLoader("./templates/pyecharts"))
from pyecharts import options as opts
from pyecharts.charts import Bar
import pandas as pd
from corescatter import scatter_bar, grade_bar
from contrastscatter import contrastscatter_bar, contrastgrade_bar
from boarditems import compamy_table, processor_table, file_item_num_bar, company_submit_num_bar, processor_submit_num_bar, update_submit_num_bar
from dw.dw import *
dw = DwUtil()

# 初始化Flask项目
app = Flask(__name__)
app.config.from_object(Config)  # Flask项目配置，在config.py中记录


# 接受请求时，释放与关闭与数据库的连接
@app.before_request
def before_request():
    g.db = sqlite3.connect(app.config["DATABASE"])


@app.teardown_request
def teardown_request(exception):
    db = getattr(g, "db", None)
    if db is not None:
        db.close()


# 以下是服务器响应请求的函数
@app.route("/")
@app.route("/index")
def index():
    return render_template("home.html")


@app.route("/login", methods=["POST", "GET"])
def login():
    if request.method == "POST":
        username = request.form["username"]  # 前端POST请求提交form的username字段
        password = request.form["password"]  # 前端POST请求提交form的password字段
        error = None
        user = g.db.execute("select * from users where username=?", (username,)).fetchone()
        if user is None:
            error = "Incorrect username."
        elif not check_password_hash(user[2], password):
            error = "Incorrect password."

        if error is None:
            # store the user id in a new session and return to the index
            session.clear()
            session["username"] = user[1]
            return redirect(url_for("get_board"))
        flash(error)

    return render_template("auth/login.html")


@app.route("/register", methods=["POST", "GET"])
def register():
    if request.method == "POST":
        username = request.form["username"]  # 前端POST请求提交form的username字段
        password = request.form["password"]  # 前端POST请求提交form的password字段
        confirmpassword = request.form["confirmpassword"] # 前端POST请求提交form的confirmpassword字段
        error = None

        if not username:
            error = "Username is required."
        elif not password:
            error = "Password is required."
        elif not confirmpassword:
            error = "Password is required."

        if error is None:
            try:
                g.db.execute(
                    "INSERT INTO users (username, password) VALUES (?, ?)",
                    (username, generate_password_hash(password)),
                )
                g.db.commit()
            except g.db.IntegrityError:
                # The username was already taken, which caused the
                # commit to fail. Show a validation error.
                error = f"User {username} is already registered."
            else:
                # Success, go to the login page.
                return redirect(url_for("login"))
        flash(error)
    return render_template("auth/register.html")


# 示例 没有用到
def bar_base() -> Bar:  # -> 表示要返回的是类型
    c = (
        Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add_xaxis(["衬衫", "羊毛衫", "雪纺衫", "裤子", "高跟鞋", "袜子"])
            .add_yaxis("商家A", [5, 20, 36, 10, 75, 90])
            .add_yaxis("商家B", [15, 25, 16, 55, 48, 8])
            .set_global_opts(
            title_opts=opts.TitleOpts(
                title="Bar-基本示例",
                subtitle="我是副标题"
            )
        )
    )
    return c


# 示例
@app.route("/chart")
def chart():
    return render_template("chart.html")


# 示例
@app.route("/barChart")
def get_bar_chart():
    c = bar_base()
    return c.dump_options_with_quotes()


# 单个cpu的散点图 响应html中的数据请求，显示pyechart
@app.route("/scatter")
def scatter():
    if request.method == "GET":
        processor = session.get("processor")
        print(processor)
        sca = scatter_bar(processor)
        return sca.dump_options_with_quotes()
    else:
        return redirect('/processor_detail')


@app.route("/grade")
def grade():
    if request.method == "GET":
        processor = session.get("processor")
        print(processor)
        c = grade_bar(processor)
        return c.dump_options_with_quotes()
    else:
        return redirect('/processor_detail')


# 单个cpu的散点图 响应网页url请求
@app.route("/corescatter")
def corescatter():
    # 读取数据
    # data = pd.read_csv("data/ods/ods_cpu2006_fprate.csv")
    from dw.dw import DwUtil
    dw = DwUtil()
    data = dw.execute_sql("SELECT * FROM dw_cpu2006_fprate")
    data = data.dropna(subset=['result', 'num_core', 'memory'])  # 删除缺失值
    data = data[data['result'] != 0]  # 删除0值
    # 按CPU型号分组
    cpu_group = data.groupby('processor')
    cpu_name = "Intel Xeon Platinum 8160"
    option_processor = cpu_group.get_group(cpu_name)
    option_processor.drop("processor", 1, inplace=True)
    cpu_num = len(option_processor)
    option_processor.rename(
        columns={'company': '公司', 'system_name': '系统名称', 'result': '测试结果(Base)', 'num_core': '核数',
                 'cpu_speed': '处理器速度', 'first_cache': '一级缓存', 'second_cache': '二级缓存',
                 'third_cache': '三级缓存', 'other_cache': '其他缓存', 'memory': '内存',
                 'updated': '更新时间', 'report_link':'报告链接'}, inplace=True)

    content = option_processor.to_html(justify='center', na_rep="NULL") #index=False,
    content = content.replace("class=\"dataframe\"", "class=\"table table-sm table-hover\" style=\"min-width:1000px;\"")
    content = content.replace("<td>http", "<td><a href=\"http")
    content = content.replace("html</td>", "html\" class=\"btn btn-primary btn-sm\" role=\"button\" aria-pressed=\"true\">详情</a></td>")

    processor_name = 'Intel Xeon Platinum 8160'
    grade_df = dw.execute_sql("SELECT * FROM processor_result WHERE processor = '{name}'".format(name=processor_name))
    grades = grade_df[
        ["int_grade", "float_grade", "single_grade", "multi_grade", "java_grade", "power_grade", "overall_grade"]]
    grades.rename(columns={'int_grade': '整数运算性能', 'float_grade': '浮点运算性能', 'single_grade': '单核性能', 'multi_grade':'多核性能', 'java_grade':'java性能', 'power_grade': 'power性能', 'overall_grade':'整体性能'}, inplace=True)
    grades = grades.to_html(index = False, justify='center', na_rep="NULL")
    grades = grades.replace("class=\"dataframe\"", "class=\"table table-sm\" style=\"min-width:1000px;\"")
    grades = grades.replace("<tr>", "<tr class=\"text-center\">")
    # print(grades)
    username = session.get("username")
    return render_template("analysis/corescatter.html", content = content, cpu_name = cpu_name, cpu_num = cpu_num, grades = grades, username = username)


# 两个CPU分数 对比 响应html中的数据请求，显示pyechart
@app.route("/congrade")
def congrade():
    processor_list = session.get("proc_list")
    grid = contrastgrade_bar(processor_list)
    return grid.dump_options_with_quotes()


# 两个CPU分数 对比 响应html中的数据请求，显示pyechart
@app.route("/conscatter")
def conscatter():
    processor_list = session.get("proc_list")
    grid = contrastscatter_bar(processor_list)
    return grid.dump_options_with_quotes()


# 对比 响应网页url请求
@app.route("/contrastscatter", methods=['GET', 'POST'])
def contrastscatter():
    if request.method == 'POST':
        print("request.form")
        print(request.form)
        processor = dict(request.form)
        processor_list = []
        for iterr in processor:
            for i in request.form.getlist(iterr):
                processor_list.append(i)
        print(processor_list)
    processor_list = processor_list[1:]
    grades = pd.DataFrame(columns=["processor", "int_grade", "float_grade", "single_grade", "multi_grade", "java_grade", "power_grade", "overall_grade"])
    for proc in processor_list:
        grade_df = dw.execute_sql("SELECT * FROM processor_result WHERE processor = '{name}'".format(name=proc))
        grades = pd.concat([grades, grade_df[
        ["processor", "int_grade", "float_grade", "single_grade", "multi_grade", "java_grade", "power_grade",
         "overall_grade"]]])

    grades.rename(
        columns={'processor': '处理器', 'int_grade': '整数运算性能', 'float_grade': '浮点运算性能', 'single_grade': '单核性能',
                 'multi_grade': '多核性能',
                 'java_grade': 'java性能', 'power_grade': 'power性能', 'overall_grade': '整体性能'}, inplace=True)
    grades = grades.to_html(index=False, justify='center', na_rep="NULL")
    grades = grades.replace("class=\"dataframe\"", "class=\"table table-sm\" style=\"min-width:1000px;\"")
    grades = grades.replace("<tr>", "<tr class=\"text-center\">")

    session["proc_list"] = processor_list
    username = session.get("username")
    return render_template("analysis/contrastscatter.html", grades = grades, username = username)


# board中
# 不同文件中的数据条数 柱状图 响应html中的数据请求，显示pyechart
@app.route("/file_item_num")
def file_item_num():
    c = file_item_num_bar()
    return c.dump_options_with_quotes()


# 不同公司提交的数据条数 前10名 饼图 响应html中的数据请求，显示pyechart
@app.route("/company_submit_num")
def company_submit_num():
    c = company_submit_num_bar()
    return c.dump_options_with_quotes()

# 不同cpu相关的数据条数 前10名 饼图 响应html中的数据请求，显示pyechart
@app.route("/processor_submit_num")
def processor_submit_num():
    c = processor_submit_num_bar()
    return c.dump_options_with_quotes()


# 不同月份更新的数据条数 柱状图 响应html中的数据请求，显示pyechart
@app.route("/update_submit_num")
def update_submit_num():
    c = update_submit_num_bar()
    return c.dump_options_with_quotes()


# 看板 响应网页url请求
@app.route("/board")
def get_board():
    processor = compamy_table()
    processor = processor.replace("class=\"dataframe\"", "class=\"table table-sm table-hover\" style=\"min-height:500px;\"")
    company = processor_table()
    company = company.replace("class=\"dataframe\"", "class=\"table table-sm table-hover\" style=\"min-height:500px; min-width:400px;\"")
    username = session.get("username")
    return render_template("analysis/board.html", processor = processor, company = company, username = username)


@app.route("/dropdown")
def dropdown():
    return render_template("dropdown.html")


@app.route('/show_data')
def go_show_data():
    sql = "select name from sqlite_master where type='table' order by name"
    tables = dw.execute_sql(sql)
    # print(tables)
    # print(database_info)
    for table_name in tables['name']:
        # print(table_name)
        if database_info.get(table_name):
            print("yes")
            # print(database_info[table_name]['in_chinese'])
    username = session.get("username")
    return render_template(
        'show_data/show_data.html',
        tables=tables,
        database_info=database_info,
        username = username
    )


@app.route('/processor_detail')
def show_processor_detail():
    # print("start")
    if request.method == 'GET':
        # print(request.args)
        # print("request")
        processor_name = request.args.get('处理器', '')
        # print(processor_name)
    sql = "select name from sqlite_master where type='table' order by name"
    tables = dw.execute_sql(sql)
    df_total = pd.DataFrame(columns=['company', 'system_name', 'result' , 'num_core' , 'processor' , 'cpu_speed' , 'first_cache' , 'second_cache' , 'third_cache' , 'other_cache' , 'memory' , 'updated' , 'report_link'])
    for index, row in tables.iterrows():
        # if row['name'] != 'users'and row['name'] != 'sqlite_master' and row['name'] != 'sqlite_sequence' and row['name'] != 'processor_result':
        if 'dw' in row['name']:
            sql1 = "select * from " + row['name'] + " where processor = \'" + processor_name + "\'"
            # print(sql1)
            df1 = dw.execute_sql(sql1)
            # print(df1)
            df1.insert(0,'benchmark',row['name'])
            df_total = pd.concat([df_total, df1])
    df_total = df_total.iloc[:, :-1]
    # df_total.to_excel('data.xlsx')
    # print(df_total)

    df_total = df_total[['benchmark', 'company', 'system_name', 'result', 'num_core', 'cpu_speed', 'first_cache',
                        'second_cache', 'third_cache', 'other_cache', 'memory', 'updated', 'report_link']];
    df_total = df_total.reset_index(drop=False)
    df_total.rename(
        columns={'company': '公司', 'system_name': '系统名称', 'result': '测试结果(Base)', 'num_core': '核数',
                 'cpu_speed': '处理器速度', 'first_cache': '一级缓存', 'second_cache': '二级缓存',
                 'third_cache': '三级缓存', 'other_cache': '其他缓存', 'memory': '内存',
                 'updated': '更新时间', 'report_link': '报告链接', 'benchmark':'基准'}, inplace=True)
    cpu_num = len(df_total)

    grade_df = dw.execute_sql("SELECT * FROM processor_result WHERE processor = '{name}'".format(name=processor_name))
    grades = grade_df[
        ["int_grade", "float_grade", "single_grade", "multi_grade", "java_grade", "power_grade", "overall_grade"]]
    grades.rename(
        columns={'int_grade': '整数运算性能', 'float_grade': '浮点运算性能', 'single_grade': '单核性能', 'multi_grade': '多核性能',
                 'java_grade': 'java性能', 'power_grade': 'power性能', 'overall_grade': '整体性能'}, inplace=True)
    grades = grades.to_html(index=False, justify='center', na_rep="NULL")
    grades = grades.replace("class=\"dataframe\"", "class=\"table table-sm\" style=\"min-width:1000px;\"")
    grades = grades.replace("<tr>", "<tr class=\"text-center\">")
    # print(grades)
    username = session.get("username")
    session["processor"] = processor_name;
    return render_template('show_data/processor_detail.html', df_total=df_total, processor_name = processor_name, cpu_num = cpu_num, grades = grades,  username = username)


@app.route('/processor_anysis_score')
def show_process_anysis_score():
    sql1 = "select processor from processor_result"
    df1 = dw.execute_sql(sql1)
    cc_lst = df1['processor'].values.tolist()
    # print(cc_lst)

    sql1 = "select * from processor_result"
    df1 = dw.execute_sql(sql1)
    df1.fillna(value=-1,inplace=True)

    "int_grade	float_grade	single_grade	multi_grade	java_grade	power_grade	overall_grade"
    df1.insert(2, 'power_grade', df1.pop('power_grade'))
    df1.insert(2, 'java_grade', df1.pop('java_grade'))
    df1.insert(2, 'multi_grade', df1.pop('multi_grade'))
    df1.insert(2, 'single_grade', df1.pop('single_grade'))
    df1.insert(2, 'float_grade', df1.pop('float_grade'))
    df1.insert(2, 'int_grade', df1.pop('int_grade'))
    df1.insert(2, 'overall_grade', df1.pop('overall_grade'))

    df1.rename(columns={'processor':'处理器','int_grade': '整数运算性能', 'float_grade': '浮点运算性能', 'single_grade': '单核性能', 'multi_grade':'多核性能', 'java_grade':'java性能', 'power_grade': 'power性能', 'overall_grade':'整体性能'}, inplace=True)

    # df1.to_csv("df.csv",index=False)
    for i in range(len(df1)):  # len(df)
        for j in range(1, len(df1.columns)):
            # print(df.iloc[i,j],type(df.iloc[i,j]))
            if (type(df1.iloc[i, j]) == float64 or type(df1.iloc[i, j]) == int or type(df1.iloc[i, j]) == np.int64 or type(
                    df1.iloc[i, j]) == float):
                # 将df的每一行每一列的数据转换为两位小数的字符串
                df1.iloc[i, j] = '%.2f' % df1.iloc[i, j]
                # print(df.iloc[i, j])
            elif (type(df1.iloc[i, j]) == str):
                try:
                    aa = float(df1.iloc[i, j])
                    df1.iloc[i, j] = '%.2f' % aa
                except:
                    aa = 1
    # print(df1.iloc[5, 2], type(df1.iloc[5,2]))
    # print(df1)
    username = session.get("username")
    return render_template('show_data/processor_score.html', tables=df1, cc_lst = cc_lst, username = username)

@app.route('/crud')
def go_crud():
    if request.method == 'GET':
        table_name = request.args.get('db_name', '')
        table_name_ch = database_info[table_name]['in_chinese']
        # print(database_info[table_name].keys())
        cc_lst1 = list(database_info[table_name]['company'].items())[0:]
        cc_lst2 = list(database_info[table_name]['system_name'].items())[0:]
        cc_lst3 = list(database_info[table_name]['result'].items())[0:]
        cc_lst4 = list(database_info[table_name]['num_core'].items())[0:]
        cc_lst5 = list(database_info[table_name]['cpu_speed'].items())[0:]
        cc_lst6 = list(database_info[table_name]['first_cache'].items())[0:]
        cc_lst7 = list(database_info[table_name]['second_cache'].items())[0:]
        cc_lst8 = list(database_info[table_name]['third_cache'].items())[0:]
        cc_lst9 = list(database_info[table_name]['other_cache'].items())[0:]
        cc_lst10 = list(database_info[table_name]['memory'].items())[0:]
        cc_lst11 = list(database_info[table_name]['updated'].items())[0:]
        cc_lst12 = list(database_info[table_name]['report_link'].items())[0:]
        cc_lst13 = list(database_info[table_name]['id'].items())[0:]
        cc_lst14 = list(database_info[table_name]['processor'].items())[0:]
        # print(cc_lst14)
        cc_lst = [cc_lst1, cc_lst2, cc_lst3, cc_lst4, cc_lst5,
                  cc_lst6, cc_lst7, cc_lst8, cc_lst9, cc_lst10, cc_lst11, cc_lst12, cc_lst13, cc_lst14]
    username = session.get("username")
    return render_template('show_data/crud.html',
                                table_name=table_name,
                                table_name_ch=table_name_ch,
                                cc_lst=cc_lst,
                                username = username)


@app.route('/crud_result', methods=['GET', 'POST'])
def go_crud_result():
    if request.method == 'POST':
        # print("request.form")
        # print(request.form)
        table_name = request.form['db_name']
        table_name_ch = database_info[table_name]['in_chinese']
        cc_lst1 = list(database_info[table_name]['company'].items())[0:]
        cc_lst2 = list(database_info[table_name]['system_name'].items())[0:]
        cc_lst3 = list(database_info[table_name]['result'].items())[0:]
        cc_lst4 = list(database_info[table_name]['num_core'].items())[0:]
        cc_lst5 = list(database_info[table_name]['cpu_speed'].items())[0:]
        cc_lst6 = list(database_info[table_name]['first_cache'].items())[0:]
        cc_lst7 = list(database_info[table_name]['second_cache'].items())[0:]
        cc_lst8 = list(database_info[table_name]['third_cache'].items())[0:]
        cc_lst9 = list(database_info[table_name]['other_cache'].items())[0:]
        cc_lst10 = list(database_info[table_name]['memory'].items())[0:]
        cc_lst11 = list(database_info[table_name]['updated'].items())[0:]
        cc_lst12 = list(database_info[table_name]['report_link'].items())[0:]
        cc_lst13 = list(database_info[table_name]['id'].items())[0:]
        cc_lst14 = list(database_info[table_name]['processor'].items())[0:]
        cc_lst = [cc_lst1, cc_lst2, cc_lst3, cc_lst4, cc_lst5,
                  cc_lst6, cc_lst7, cc_lst8, cc_lst9, cc_lst10, cc_lst11, cc_lst12, cc_lst13,cc_lst14]

        # 去数据库里拿数据
        result_data = query_data(request.form)
        result_data.rename(
            columns={'company': '公司', 'system_name': '系统名称', 'result': '测试结果(Base)', 'num_core': '核数',
                     'cpu_speed': '处理器速度', 'first_cache': '一级缓存', 'second_cache': '二级缓存',
                     'third_cache': '三级缓存', 'other_cache': '其他缓存', 'memory': '内存',
                     'updated': '更新时间', 'report_link': '报告链接', 'processor': '处理器'}, inplace=True)

        content = result_data.to_html(justify='center', index=False, na_rep="NULL")  # index=False,
        content = content.replace("class=\"dataframe\"",
                                  "class=\"table table-sm table-hover\" style=\"min-width:1000px;\"")
        content = content.replace("<td>http", "<td><a href=\"http")
        content = content.replace("html</td>",
                                  "html\" class=\"btn btn-outline-primary btn-sm\" role=\"button\" aria-pressed=\"true\">SPEC</a></td>")

        # # print(result_data)
        # # print("********************")
        # for col in result_data.columns.tolist()[0:]:
        #     print(col)

        c_c_dict = database_info[table_name]['col_name']
        c_c_dict_render = c_c_dict
        username = session.get("username")
        return render_template('show_data/crud_result.html',
                               table_name=table_name,
                               table_name_ch=table_name_ch,
                               cc_lst=cc_lst,
                               c_c_dict=c_c_dict_render,
                               result_data=content,
                               username = username)

# 程序执行的入口
if __name__ == "__main__":
    app.run(debug=True)
